USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorSalesInvoice]    Script Date: 04/08/2014 00:55:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpTractorSalesInvoice]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpTractorSalesInvoice]
GO

USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorSalesInvoice]    Script Date: 04/08/2014 00:55:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROC [dbo].[SpTractorSalesInvoice] --2
(
@INVOICE_ID INT
)
AS
BEGIN
SELECT CASE TRACTOR_ENGINE_NO
		WHEN '-' THEN 0
		ELSE SI.SALES_INVOICE_ID
	    END  AS INVOICE_NO
       ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE  
        ,INVOICE_DISCOUNT 
         ,CASE TRACTOR_ENGINE_NO
		WHEN '-' THEN '0.0'
		--ELSE Cast(CONVERT(DECIMAL(10,1),INVOICE_VAT_PERCENT) as nvarchar) 
		ELSE '' 
	   END AS INVOICE_VAT_PERCENT  
       ,CASE TRACTOR_ENGINE_NO
		WHEN '-' THEN 0
		ELSE INVOICE_GRANDTOTAL
	   END AS INVOICE_GRANDTOTAL
      ,SI.CUSTOMER_ID AS CUSTOMER_ID 
       ,DC_NO
       ,replace(convert(NVARCHAR, DC_DATE, 106), ' ', '-')AS DC_DATE
       ,SELL_TYPE
       ,TRACTOR_ENGINE_NO
       ,TRACTOR_CHASSIS_NO
       ,TRACTOR_SPECIFICATION
        ,TRACTOR_MODEL_NAME 
        ,HYPOTHETICATION
        ,CASE TRACTOR_ENGINE_NO
		WHEN '-' THEN '0'
		ELSE dbo.fNumToWords(INVOICE_GRANDTOTAL)
	END  AS AMOUNT_IN_WORDS
        
       
 FROM SALES_INVOICES SI
     --LEFT OUTER JOIN CUSTOMER C ON C.CUSTOMER_ID =SI.CUSTOMER_ID
     LEFT OUTER JOIN  DELIVERY_CHALAN DC ON DC.CUSTOMER_ID=SI.CUSTOMER_ID AND SI.SALES_INVOICE_ID=DC.SALES_INVOICE_ID
     LEFT OUTER JOIN TRACTOR_PURCHASES TP ON TP.TRACTOR_ID=DC.TRACTOR_ID
     LEFT OUTER JOIN TRACTOR_MODELS TM ON TM.TRACTOR_MODEL_ID=TP.TRACTOR_MODEL_ID
     --LEFT OUTER JOIN  VILLAGES V ON V.VILLAGE_ID=C.VILLAGE_ID
     
     
 WHERE SI.SALES_INVOICE_ID=@INVOICE_ID  AND SELL_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='TRACTOR')
 ORDER BY TRACTOR_ENGINE_NO DESC
END




GO


